package dao;

/**
 *
 * @author yakimovich
 */
import bean.User;
import interfaces.UserDao;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.logging.LogFactory;

/**
 *
 * @author Yana
 */
public class JdbcUserDao extends AbstractJdbcDao
        implements UserDao {

	/**
	 * Create JdbcUserDao by default.
	 */
	public JdbcUserDao() {
		super();
	}

	/**
	 * Create JdbcUserDao with the specified parameters.
	 * 
	 * @param settings
	 */
	public JdbcUserDao(String settings) {
		super(settings);
	}
	
	/**
	 * Create logger.
	 */
	private static org.apache.commons.logging.Log logger 
			= LogFactory.getLog(JdbcUserDao.class);

	/**
	 * Create in the table users a row with the specified user.
	 *
	 * @param user
	 */
	@Override
	public void create(User user) {
		if (user == null) {
			throw new NullPointerException();
		}

		Connection conn = createConnection();
		PreparedStatement ps = null;

		long id = user.getId();
		String login = user.getLogin();
		String password = user.getPassword();
		String email = user.getEmail();
		String firstName = user.getFirstName();
		String lastName = user.getLastName();
		Date birthday = user.getBirthday();
		long role_id = user.getRole().getId();

		try {
			ps = conn.prepareStatement("INSERT INTO USERS ("
					+ "LOGIN, PASSWORD, EMAIL,"
					+ "FIRST_NAME, LAST_NAME, "
					+ "BIRTHDAY, ROLE_ID) "
					+ "VALUES (?, ?, ?, ?, ?, ?, ?);");
			ps.setString(1, login);
			ps.setString(2, password);
			ps.setString(3, email);
			ps.setString(4, firstName);
			ps.setString(5, lastName);
			ps.setDate(6, birthday);
			ps.setLong(7, role_id);

			int result = ps.executeUpdate();
		} catch (SQLException e) {
			logger.error(this, e);
			JdbcUtils.rollbackQuietly(conn);
			throw new RuntimeException(e);
		} finally {
			JdbcUtils.closeQuietly(ps);
			JdbcUtils.closeQuietly(conn);
		}
	}

	/**
	 * Update in the table users a row with the specified user.
	 *
	 * @param user
	 */
	@Override
	public void update(User user) {
		if (user == null) {
			throw new NullPointerException();
		}
		if (!isUserById(user.getId())) {
			throw new IllegalArgumentException();
		}

		Connection conn = createConnection();
		PreparedStatement ps = null;

		long id = user.getId();
		String login = user.getLogin();
		String password = user.getPassword();
		String email = user.getEmail();
		String firstName = user.getFirstName();
		String lastName = user.getLastName();
		Date birthday = user.getBirthday();
		long role_id = user.getRole().getId();

		try {
			ps = conn.prepareStatement("UPDATE Users SET "
					+ "LOGIN = ?, PASSWORD = ?, EMAIL = ?, "
					+ "FIRST_NAME = ?, LAST_NAME = ?, "
					+ "BIRTHDAY = ?, "
					+ "ROLE_ID = ? " + "WHERE User_id = ?");

			ps.setString(1, login);
			ps.setString(2, password);
			ps.setString(3, email);
			ps.setString(4, firstName);
			ps.setString(5, lastName);
			ps.setDate(6, birthday);
			ps.setLong(7, role_id);
			ps.setLong(8, id);

			int result = ps.executeUpdate();
		} catch (SQLException e) {
			logger.error(this, e);
			JdbcUtils.rollbackQuietly(conn);
			throw new RuntimeException(e);
		} finally {
			JdbcUtils.closeQuietly(ps);
			JdbcUtils.closeQuietly(conn);
		}
	}

	/**
	 * Remove in the table users a row with the specified user.
	 *
	 * @param user
	 */
	@Override
	public void remove(User user) {
		if (user == null) {
			throw new NullPointerException();
		}
		if (!isUserById(user.getId())) {
			throw new IllegalArgumentException();
		}
		Connection conn = createConnection();
		PreparedStatement ps = null;

		long id = user.getId();

		try {
			ps = conn.prepareStatement(
				"DELETE FROM Users WHERE User_id = ?");
			ps.setLong(1, id);

			int result = ps.executeUpdate();
		} catch (SQLException e) {
			logger.error(this, e);
			JdbcUtils.rollbackQuietly(conn);
			throw new RuntimeException(e);
		} finally {
			JdbcUtils.closeQuietly(ps);
			JdbcUtils.closeQuietly(conn);
		}
	}

	/**
	 * Find in the table users all user.
	 *
	 * @return List of all users
	 */
	@Override
	public List<User> findAll() {
		List<User> result = new ArrayList<User>();
		Connection conn = createConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = conn.prepareStatement("SELECT * FROM Users "
					+ "LEFT JOIN Roles "
					+ "ON Users.Role_id = Roles.Role_id");
			rs = ps.executeQuery();
			while (rs.next()) {
				long id = rs.getLong("USER_ID");
				String login = rs.getString("LOGIN");
				String password = rs.getString("PASSWORD");
				String email = rs.getString("EMAIL");
				String firstName = rs.getString("FIRST_NAME");
				String lastName = rs.getString("LAST_NAME");
				Date birthday = rs.getDate("BIRTHDAY");
				String role_name = rs.getString("NAME");

				User user = new User();
				user.setId(id);
				user.setLogin(login);
				user.setPassword(password);
				user.setEmail(email);
				user.setFirstName(firstName);
				user.setLastName(lastName);
				user.setBirthday(birthday);
				user.setRole(new JdbcRoleDao()
					.findByName(role_name));

				result.add(user);
			}
			return result;
		} catch (SQLException e) {
			logger.error(this, e);
			JdbcUtils.rollbackQuietly(conn);
			throw new RuntimeException(e);
		} finally {
			JdbcUtils.closeQuietly(rs);
			JdbcUtils.closeQuietly(ps);
			JdbcUtils.closeQuietly(conn);
		}
	}

	/**
	 * Find in the table users a user with the specified login.
	 *
	 * @param login
	 *            the specified login of a user
	 * @return user with the specified login
	 */
	@Override
	public User findByLogin(String login) {
		if (login == null) {
			throw new NullPointerException();
		}
		if (!isUserByLogin(login)) {
			throw new IllegalArgumentException();
		}
		User user = null;
		Connection conn = createConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = conn.prepareStatement("SELECT * FROM Users "
					+ "LEFT JOIN Roles "
					+ "ON Users.Role_id = Roles.Role_id "
					+ "WHERE login = ?");
			ps.setString(1, login);
			rs = ps.executeQuery();

			while (rs.next()) {
				long id = rs.getLong("USER_ID");
				String password = rs.getString("PASSWORD");
				String email = rs.getString("EMAIL");
				String firstName = rs.getString("FIRST_NAME");
				String lastName = rs.getString("LAST_NAME");
				Date birthday = rs.getDate("BIRTHDAY");
				String role_name = rs.getString("NAME");

				user = new User();
				user.setId(id);
				user.setLogin(login);
				user.setPassword(password);
				user.setEmail(email);
				user.setFirstName(firstName);
				user.setLastName(lastName);
				user.setBirthday(birthday);
				user.setRole(new JdbcRoleDao()
					.findByName(role_name));
			}
			return user;
		} catch (SQLException e) {
			logger.error(this, e);
			JdbcUtils.rollbackQuietly(conn);
			throw new RuntimeException(e);
		} finally {
			JdbcUtils.closeQuietly(rs);
			JdbcUtils.closeQuietly(ps);
			JdbcUtils.closeQuietly(conn);
		}
	}

	/**
	 * Find in the table users a user with the specified email.
	 *
	 * @param email
	 *            the specified email of a user
	 * @return user with the specified email
	 */
	@Override
	public User findByEmail(String email) {
		if (email == null) {
			throw new NullPointerException();
		}
		if (!isUserByEmail(email)) {
			throw new IllegalArgumentException();
		}
		User user = null;
		Connection conn = createConnection();

		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement("SELECT * FROM Users "
					+ "LEFT JOIN Roles "
					+ "ON Users.Role_id = Roles.Role_id "
					+ "WHERE email = ?");
			ps.setString(1, email);
			rs = ps.executeQuery();

			while (rs.next()) {
				long id = rs.getLong("USER_ID");
				String login = rs.getString("LOGIN");
				String password = rs.getString("PASSWORD");
				String firstName = rs.getString("FIRST_NAME");
				String lastName = rs.getString("LAST_NAME");
				Date birthday = rs.getDate("BIRTHDAY");
				String role_name = rs.getString("NAME");

				user = new User();
				user.setId(id);
				user.setLogin(login);
				user.setPassword(password);
				user.setEmail(email);
				user.setFirstName(firstName);
				user.setLastName(lastName);
				user.setBirthday(birthday);
				user.setRole(new JdbcRoleDao()
					.findByName(role_name));
			}
			return user;
		} catch (SQLException e) {
			logger.error(this, e);
			JdbcUtils.rollbackQuietly(conn);
			throw new RuntimeException(e);
		} finally {
			JdbcUtils.closeQuietly(rs);
			JdbcUtils.closeQuietly(ps);
			JdbcUtils.closeQuietly(conn);
		}
	}

	private boolean isUserById(Long user_id) {

		Connection conn = createConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = conn.prepareStatement("SELECT * FROM Users "
					+ "WHERE User_id = ?");
			ps.setLong(1, user_id);
			rs = ps.executeQuery();

			if (rs.next()) {
				return true;
			}
		} catch (SQLException e) {
			logger.error(this, e);
			throw new RuntimeException(e);
		} finally {
			JdbcUtils.closeQuietly(rs);
			JdbcUtils.closeQuietly(ps);
			JdbcUtils.closeQuietly(conn);
		}
		return false;
	}

	private boolean isUserByLogin(String login) {

		Connection conn = createConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = conn.prepareStatement("SELECT * FROM Users "
					+ "WHERE login = ?");
			ps.setString(1, login);
			rs = ps.executeQuery();

			if (rs.next()) {
				return true;
			}
		} catch (SQLException e) {
			logger.error(this, e);
			throw new RuntimeException(e);
		} finally {
			JdbcUtils.closeQuietly(rs);
			JdbcUtils.closeQuietly(ps);
			JdbcUtils.closeQuietly(conn);
		}
		return false;
	}

	private boolean isUserByEmail(String email) {

		Connection conn = createConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = conn.prepareStatement("SELECT * FROM Users "
					+ "WHERE Email = ?");
			ps.setString(1, email);
			rs = ps.executeQuery();

			if (rs.next()) {
				return true;
			}
		} catch (SQLException e) {
			logger.error(this, e);
			throw new RuntimeException(e);
		} finally {
			JdbcUtils.closeQuietly(rs);
			JdbcUtils.closeQuietly(ps);
			JdbcUtils.closeQuietly(conn);
		}
		return false;
	}
}
